﻿Public Class TaxonNameData
    Implements INamesData

    Private Shared ConnectionString As String = Configuration.ConfigurationManager.ConnectionStrings("names").ConnectionString
    Private Shared UnknwonTaxaGuid As String

#Region "Names"


    Private Function LoadNameFromDs(ByVal nameTbl As Name) As TaxonNamesInterface.Name
        Dim newName As New TaxonNamesInterface.Name

        newName.Authors = nameTbl.Authors
        newName.BasionymAuthors = nameTbl.BasionymAuthors
        newName.Canonical = nameTbl.Canonical
        newName.CombinationAuthors = nameTbl.CombinationAuthors
        If nameTbl.Illegitimate.HasValue Then newName.Illegitimate = nameTbl.Illegitimate.Value
        If nameTbl.InCitation.HasValue Then newName.InCitation = nameTbl.InCitation.Value
        If nameTbl.Invalid.HasValue Then newName.Invalid = nameTbl.Invalid.Value
        newName.MicroReference = nameTbl.MicroReference
        If nameTbl.Misapplied.HasValue Then newName.Misapplied = nameTbl.Misapplied.Value
        newName.NameFull = nameTbl.NameFull
        'todo ? newName.NameFullFormatted 
        newName.NameId = nameTbl.NameId
        newName.NameLSID = nameTbl.NameLSID
        newName.Orthography = nameTbl.Orthography
        If nameTbl.ProParte.HasValue Then newName.ProParte = nameTbl.ProParte.Value
        newName.PublishedIn = nameTbl.PublishedIn
        If nameTbl.RankId.HasValue Then
            newName.RankId = nameTbl.RankId.Value
            newName.NameRank = RankById(newName.RankId)
        End If
        If nameTbl.ReferenceId.HasValue Then newName.ReferenceId = nameTbl.ReferenceId.Value
        newName.TypeVoucher = nameTbl.TypeVoucher
        newName.YearOfPublication = nameTbl.YearOfPublication
        newName.YearOnPublication = nameTbl.YearOnPublication

        If nameTbl.BasedOnId.HasValue Then
            newName.BasedOnId = nameTbl.BasedOnId
        End If
        If nameTbl.BasionymId.HasValue Then
            newName.BasionymId = nameTbl.BasionymId
        End If
        If nameTbl.BlockingNameId.HasValue Then
            newName.BlockingNameId = nameTbl.BlockingNameId
        End If
        If nameTbl.ConservedAgainstId.HasValue Then
            newName.ConservedAgainstId = nameTbl.ConservedAgainstId
        End If
        If nameTbl.HomonymOfId.HasValue Then
            newName.HomonymOfId = nameTbl.HomonymOfId
        End If
        If nameTbl.ParentNameId.HasValue Then
            newName.ParentNameId = nameTbl.ParentNameId.Value
        End If
        If nameTbl.PreferredNameId.HasValue Then
            newName.PreferredNameId = nameTbl.PreferredNameId
        End If
        If nameTbl.ReplacementForId.HasValue Then
            newName.ReplacementForId = nameTbl.ReplacementForId
        End If
        If nameTbl.TypeNameId.HasValue Then
            newName.TypeNameId = nameTbl.TypeNameId
        End If

        Return newName
    End Function

    Public Function GetNameFull(ByVal nameId As Guid) As String Implements INamesData.GetNameFull
        Dim db As New NamesDB(ConnectionString)
        Dim nm = (From tbl In db.Names Where tbl.NameId = nameId Select tbl.NameFull).First
        Return nm
    End Function

    Public Function GetName(ByVal nameId As Guid) As TaxonNamesInterface.Name Implements INamesData.GetName
        Dim db As New NamesDB(ConnectionString)

        Dim nm = From nmTbl In db.Names Where nmTbl.NameId = nameId

        Return LoadNameFromDs(nm.First)
    End Function


    Public Function GetNameChildren(ByVal nameId As Guid) As List(Of TaxonNamesInterface.Name) Implements INamesData.GetNameChildren
        Dim children As New List(Of TaxonNamesInterface.Name)

        Dim db As New NamesDB(ConnectionString)

        Dim names = _
        From tbl In db.Names Where tbl.ParentNameId = nameId Select tbl

        For Each nm As Name In names
            Dim n As TaxonNamesInterface.Name = LoadNameFromDs(nm)
            children.Add(n)
        Next

        Return children
    End Function


    Public Sub DeleteNameRecord(ByVal nameLsid As String, ByVal newNameLsid As String, ByVal user As String) Implements INamesData.DeleteNameRecord
        Dim db As New NamesDB(ConnectionString)

        Dim nm As Name = From tbl In db.Names Where tbl.NameLSID = nameLsid Select tbl

        db.Names.DeleteOnSubmit(nm)

        Dim dep As New Deprecated
        dep.Date = DateTime.Now
        dep.DeprecatedBy = user
        dep.DeprecatedLSID = nameLsid
        dep.NewLSID = newNameLsid
        dep.DeprecatedTable = "Name"

        db.Deprecateds.InsertOnSubmit(dep)

        db.SubmitChanges()

    End Sub

    Public Sub InsertUpdateName(ByVal n As TaxonNamesInterface.Name, ByVal user As String) Implements INamesData.InsertUpdateName
        Dim db As New NamesDB(ConnectionString)

        Dim newName As New Name

        If n.NameId = Guid.Empty Then
            newName.NameId = Guid.NewGuid
            db.Names.InsertOnSubmit(newName)
        Else
            newName = (From tbl In db.Names Where tbl.NameId = n.NameId Select tbl).First
        End If

        newName.Authors = n.Authors
        newName.BasedOnId = n.BasedOnId
        newName.BasionymAuthors = n.BasionymAuthors
        newName.BasionymId = n.BasionymId
        newName.BlockingNameId = n.BlockingNameId
        newName.Canonical = n.Canonical
        newName.ChangeDate = DateTime.Now
        newName.ChangeUser = user
        newName.CombinationAuthors = n.CombinationAuthors
        newName.ConservedAgainstId = n.ConservedAgainstId
        newName.HomonymOfId = n.HomonymOfId
        newName.Illegitimate = n.Illegitimate
        newName.InCitation = n.InCitation
        newName.Invalid = n.Invalid
        newName.MicroReference = n.MicroReference
        newName.Misapplied = n.Misapplied
        newName.NameFull = n.NameFull
        newName.NameLSID = TaxonNamesInterface.Name.CreateLSID(newName.NameId)
        newName.Orthography = n.Orthography
        newName.ParentNameId = n.ParentNameId
        newName.PreferredNameId = n.PreferredNameId
        newName.ProParte = n.ProParte
        newName.PublishedIn = n.PublishedIn
        newName.RankId = n.RankId
        newName.ReferenceId = n.ReferenceId
        newName.ReplacementForId = n.ReplacementForId
        newName.TypeNameId = n.TypeNameId
        newName.TypeVoucher = n.TypeVoucher
        newName.YearOfPublication = n.YearOfPublication
        newName.YearOnPublication = n.YearOnPublication

        db.SubmitChanges()

        n.NameId = newName.NameId
        n.NameLSID = newName.NameLSID
        n.ChangeDate = newName.ChangeDate
        n.ChangeUser = newName.ChangeUser

    End Sub

    Public Sub DeleteNameAuthors(ByVal nameId As Guid) Implements INamesData.DeleteNameAuthors
        Dim db As New NamesDB(ConnectionString)

        Dim auths = From tbl In db.NameAuthors Where tbl.NameId = nameId Select tbl
        db.NameAuthors.DeleteOnSubmit(auths)

        db.SubmitChanges()
    End Sub

    Private Function LoadNameAuthorFromDs(ByVal na As NameAuthor) As TaxonNamesInterface.NameAuthor
        Dim nameAuth As New TaxonNamesInterface.NameAuthor

        'todo

        Return nameAuth
    End Function

    Public Function InsertUpdateNameAuthor(ByVal nameAuthorId As Integer, ByVal nameId As Guid, ByVal basAuthors As String, ByVal combAuthors As String, ByVal basExAuth As String, ByVal combExAuth As String, ByVal isCorrected As Boolean, ByVal user As String) As TaxonNamesInterface.NameAuthor Implements INamesData.InsertUpdateNameAuthor
        Dim na As New TaxonNamesInterface.NameAuthor

        Dim db As New NamesDB(ConnectionString)
        Dim dbNa As New NameAuthor

        If nameAuthorId > 0 Then
            dbNa = From tbl In db.NameAuthors Where tbl.NameAuthorsId = nameAuthorId Select tbl
        End If

        dbNa.BasionymAuthors = basAuthors
        dbNa.BasionymEx = basExAuth
        dbNa.ChangeDate = DateTime.Now
        dbNa.ChangeUser = user
        dbNa.CombinationAuthors = combAuthors
        dbNa.CombinationEx = combExAuth
        dbNa.IsCorrected = isCorrected
        dbNa.NameAuthorsId = nameAuthorId
        dbNa.NameId = nameId

        If nameAuthorId <= 0 Then
            db.NameAuthors.InsertOnSubmit(dbNa)
        End If

        db.SubmitChanges()

        na = LoadNameAuthorFromDs(dbNa)

        Return na
    End Function

    Public Function GetNameAuthors(ByVal nameId As Guid) As List(Of TaxonNamesInterface.NameAuthor) Implements INamesData.GetNameAuthors
        Dim nas As New List(Of TaxonNamesInterface.NameAuthor)
        Dim db As New NamesDB(ConnectionString)

        Dim naDb = From tbl In db.NameAuthors Where tbl.NameId = nameId Select tbl
        For Each auth As NameAuthor In naDb
            Dim nameAuth As TaxonNamesInterface.NameAuthor = LoadNameAuthorFromDs(auth)
            nas.Add(nameAuth)
        Next

        Return nas
    End Function


    Public Function SearchNames(ByVal searchText As String, ByVal anywhereInText As Boolean) As List(Of TaxonNamesInterface.Name) Implements INamesData.SearchNames
        Dim names As New List(Of TaxonNamesInterface.Name)

        Dim db As New NamesDB(ConnectionString)

        Dim ds
        If anywhereInText Then
            ds = From tbl In db.Names Select tbl Where tbl.NameFull.Contains(searchText)
        Else
            ds = From tbl In db.Names Select tbl Where tbl.NameFull.StartsWith(searchText)
        End If

        For Each dbnm As Name In ds
            Dim nm As TaxonNamesInterface.Name = LoadNameFromDs(dbnm)
            names.Add(nm)
        Next

        Return names
    End Function

    Public Function GetNameSynonyms(ByVal nameId As Guid) As List(Of TaxonNamesInterface.Name) Implements INamesData.GetNameSynonyms
        Dim names As New List(Of TaxonNamesInterface.Name)

        Dim db As New NamesDB(ConnectionString)

        Dim ds = From nm In db.Names _
                 Join c1 In db.Concepts On nm.NameId Equals c1.NameId _
                 Join cr In db.ConceptRelationships On cr.Concept1Id Equals c1.ConceptId _
                 Join c2 In db.Concepts On c2.ConceptId Equals cr.Concept2Id _
                 Where c2.NameId = nameId

        For Each nm In ds
            names.Add(LoadNameFromDs(nm.nm))
        Next

        Return names
    End Function

    Public Function GetNameLSIDs(ByVal pageNumber As Integer, ByVal pageSize As Integer) As List(Of String) Implements INamesData.GetNameLSIDs
        Dim lsids As New List(Of String)
        Dim db As New NamesDB(ConnectionString)
        Dim total As Integer = (pageNumber - 1) * pageSize

        Dim ls = From tbl In db.Names.Take(total) Select tbl.NameLSID

        For Each lsid As String In ls
            lsids.Add(lsid)
        Next

        Return lsids
    End Function

    'todo ?
    'Public Function GetChildNames(ByVal parentNameId As Guid, ByVal recurseChildren As Boolean) As List(Of TaxonNamesInterface.Name)
    '    Dim names As New List(Of TaxonNamesInterface.Name)
    '    Dim db As New NamesDB(ConnectionString)



    '    Return names
    'End Function


#End Region


#Region "Field Status"

    Public Sub InsertUpdateFieldStatus(ByVal recordId As String, ByVal fieldStatusId As Integer, ByVal statusLevelFk As Integer, ByVal user As String) Implements INamesData.InsertUpdateFieldStatus
        Dim db As New NamesDB(ConnectionString)

        Dim fs As New FieldStatus

        If fieldStatusId > 0 Then
            fs = (From tbl In db.FieldStatus Select tbl Where tbl.FieldStatusId = fieldStatusId).First
            fs.FieldStatusId = fieldStatusId
        End If

        fs.RecordId = recordId
        fs.ChangeUser = user
        fs.ChangeDate = DateTime.Now
        fs.StatusLevelId = statusLevelFk

        If fieldStatusId > 0 Then
            db.FieldStatus.InsertOnSubmit(fs)
        End If

        db.SubmitChanges()
    End Sub

    'Public Sub InsertFieldStatus(ByVal recordId As String, ByVal tableName As String, ByVal fieldName As String, ByVal statusLevelFk As Integer, ByVal user As String)
    '    Using cnn As New SqlConnection(ConnectionString)
    '        cnn.Open()

    '        Using cmd As SqlCommand = cnn.CreateCommand()
    '            cmd.CommandText = "spInsert_FieldStatus"
    '            cmd.CommandType = CommandType.StoredProcedure
    '            cmd.Parameters.Add("@recordId", SqlDbType.NVarChar).Value = recordId
    '            cmd.Parameters.Add("@tableName", SqlDbType.NVarChar).Value = tableName
    '            cmd.Parameters.Add("@fieldName", SqlDbType.NVarChar).Value = fieldName
    '            cmd.Parameters.Add("@statusLevelFk", SqlDbType.Int).Value = Utility.GetDBInt(statusLevelFk)
    '            cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = user

    '            cmd.ExecuteNonQuery()
    '        End Using

    '        If cnn.State <> ConnectionState.Closed Then cnn.Close()
    '    End Using
    'End Sub

#End Region

#Region "Concept"

    Private Function LoadConceptFromDs(ByVal dbc As Concept) As TaxonNamesInterface.Concept
        Dim conc As New TaxonNamesInterface.Concept
        Dim rdb As New ReferencesDB(ConnectionString)

        conc.ConceptId = dbc.ConceptId

        Dim accTo = From tbl In rdb.References Select tbl Where tbl.ReferenceId = dbc.AccordingToId
        If accTo IsNot Nothing Then
            conc.AccordingTo = accTo.First.Citation
            conc.AccordingToId = dbc.AccordingToId
        End If
        conc.ChangeDate = dbc.ChangeDate
        conc.ChangeUser = dbc.ChangeUser
        conc.ConceptLSID = dbc.ConceptLSID

        Dim ndb As New NamesDB(ConnectionString)
        Dim nm = From tbl In ndb.Names Select tbl Where tbl.NameId = dbc.NameId
        If nm IsNot Nothing Then
            conc.TaxonNameId = dbc.NameId
            conc.TaxonName = nm.First.NameFull
        End If

        Return conc
    End Function

    Public Function GetConcept(ByVal conceptId As Guid) As TaxonNamesInterface.Concept Implements INamesData.GetConcept
        Dim conc As TaxonNamesInterface.Concept

        Dim db As New NamesDB(ConnectionString)
        Dim c = From tbl In db.Concepts Select tbl Where tbl.ConceptId = conceptId

        conc = LoadConceptFromDs(c)

        Return conc
    End Function

    Public Sub UpdateConcept(ByVal conc As TaxonNamesInterface.Concept, ByVal user As String) Implements INamesData.UpdateConcept
        Dim db As New NamesDB(ConnectionString)

        Dim dbc As New Concept
        dbc.ConceptLSID = conc.ConceptLSID

        If conc.ConceptId <> Guid.Empty Then
            dbc = (From tbl In db.Concepts Select tbl Where tbl.ConceptId = conc.ConceptId).First
        Else
            dbc.ConceptId = Guid.NewGuid
            dbc.ConceptLSID = TaxonNamesInterface.Concept.CreateLSID(dbc.ConceptId)

            db.Concepts.InsertOnSubmit(dbc)
        End If

        dbc.AccordingToId = conc.AccordingToId
        dbc.ChangeDate = DateTime.Now
        dbc.ChangeUser = user

        db.SubmitChanges()

        conc.ConceptId = dbc.ConceptId
        conc.ConceptLSID = dbc.ConceptLSID
        conc.ChangeDate = dbc.ChangeDate
        conc.ChangeUser = dbc.ChangeUser
    End Sub


#End Region

#Region "ConceptRelationship"

    Public Function GetRelationshipTypes() As List(Of TaxonNamesInterface.RelationshipType) Implements INamesData.GetRelationshipTypes
        Dim rels As New List(Of TaxonNamesInterface.RelationshipType)
        Dim db As New NamesDB(ConnectionString)

        Dim ds = From tbl In db.RelationshipTypes Select tbl

        For Each r As RelationshipType In ds
            Dim rt As New TaxonNamesInterface.RelationshipType
            rt.RelationshipTypeId = r.RelationshipTypeId
            rt.Description = r.Description
            rt.Inverse = r.Inverse
            rt.Name = r.Name
            rt.TCSInverse = r.TCSInverse
            rt.TCSName = r.TCSName
            rels.Add(rt)
        Next

        Return rels
    End Function

    Public Function GetNameConcepts(ByVal nameId As Guid) As List(Of TaxonNamesInterface.Concept) Implements INamesData.GetNameConcepts
        Dim concs As New List(Of TaxonNamesInterface.Concept)
        Dim db As New NamesDB(ConnectionString)

        Dim ds = From tbl In db.Concepts Where tbl.NameId = nameId Select tbl
        For Each c As Concept In ds
            concs.Add(LoadConceptFromDs(c))
        Next

        Return concs
    End Function

    Private Function LoadConceptRelationshipFromDs(ByVal dbCr As ConceptRelationship) As TaxonNamesInterface.ConceptRelationship
        Dim cr As New TaxonNamesInterface.ConceptRelationship

        cr.Concept1Id = dbCr.Concept1Id
        cr.Concept2Id = dbCr.Concept2Id
        cr.ChangeDate = dbCr.ChangeDate
        cr.ChangeUser = dbCr.ChangeUser
        cr.ConceptRelationshipId = dbCr.ConceptRelationshipId
        cr.ConceptRelationshipLSID = dbCr.ConceptRelationshipLSID
        cr.HybridOrder = dbCr.HybridOrder
        cr.RelationshipId = dbCr.RelationshipTypeId

        Return cr
    End Function

    Public Function GetNameConceptRelationshipRecords(ByVal nameId As Guid, ByVal includeToConcepts As Boolean) As List(Of TaxonNamesInterface.ConceptRelationship) Implements INamesData.GetNameConceptRelationshipRecords
        Dim crs As New List(Of TaxonNamesInterface.ConceptRelationship)

        Dim db As New NamesDB(ConnectionString)

        Dim ds = From tbl In db.Concepts _
                 Join cr In db.ConceptRelationships On cr.Concept1Id Equals tbl.ConceptId _
                 Where tbl.NameId = nameId _
                 Select cr

        For Each cr As ConceptRelationship In ds
            crs.Add(LoadConceptRelationshipFromDs(cr))
        Next

        Return crs
    End Function

    Public Function GetConceptRelationship(ByVal ConceptRelId As Guid) As TaxonNamesInterface.ConceptRelationship Implements INamesData.GetConceptRelationship
        Dim cr As TaxonNamesInterface.ConceptRelationship

        Dim db As New NamesDB(ConnectionString)

        Dim ds = From tbl In db.ConceptRelationships Select tbl Where tbl.ConceptRelationshipId = ConceptRelId

        cr = LoadConceptRelationshipFromDs(ds.First)

        Return cr
    End Function

    Public Sub UpdateConceptRelationship(ByVal cr As TaxonNamesInterface.ConceptRelationship, ByVal user As String) Implements INamesData.UpdateConceptRelationship
        Dim db As New NamesDB(ConnectionString)

        Dim dbCr As New ConceptRelationship
        If cr.ConceptRelationshipId = Guid.Empty Then
            dbCr.ConceptRelationshipId = Guid.NewGuid
            dbCr.ConceptRelationshipLSID = TaxonNamesInterface.ConceptRelationship.CreateLSID(dbCr.ConceptRelationshipId)

            db.ConceptRelationships.InsertOnSubmit(dbCr)
        Else
            dbCr = (From tbl In db.ConceptRelationships Select tbl Where tbl.ConceptRelationshipId = cr.ConceptRelationshipId).First
        End If

        dbCr.ChangeDate = DateTime.Now
        dbCr.ChangeUser = user
        dbCr.Concept1Id = cr.Concept1Id
        dbCr.Concept2Id = cr.Concept2Id
        dbCr.HybridOrder = cr.HybridOrder
        dbCr.RelationshipTypeId = cr.RelationshipId

        db.SubmitChanges()

        cr.ChangeDate = dbCr.ChangeDate
        cr.ChangeUser = dbCr.ChangeUser
        cr.ConceptRelationshipId = dbCr.ConceptRelationshipId
        cr.ConceptRelationshipLSID = dbCr.ConceptRelationshipLSID

    End Sub

    Public Sub DeleteConceptRelationship(ByVal ConceptRelLSID As String, ByVal NewConceptRelLSID As String, ByVal user As String) Implements INamesData.DeleteConceptRelationship

        'todo 
    End Sub

#End Region

#Region "Import"
    'todo

    'Private Shared ConnectionString As String = Configuration.ConfigurationManager.ConnectionStrings("names").ConnectionString

    'Public Shared Function GetImportTypes() As ImportType()
    '    Dim its As New ArrayList
    '    Using cnn As New SqlConnection(ConnectionString)
    '        cnn.Open()

    '        Using cmd As SqlCommand = cnn.CreateCommand()
    '            cmd.CommandText = "sprSelect_ImportTypes"
    '            cmd.CommandType = CommandType.StoredProcedure

    '            Dim ds As New DataSet
    '            Dim da As New SqlDataAdapter(cmd)
    '            da.Fill(ds)

    '            If ds.Tables.Count > 0 Then
    '                For Each row As DataRow In ds.Tables(0).Rows
    '                    its.Add(New ImportType(row))
    '                Next
    '            End If
    '        End Using

    '        If cnn.State <> ConnectionState.Closed Then cnn.Close()
    '    End Using

    '    Return its.ToArray(GetType(ImportType))
    'End Function

    'Public Shared Function InsertUpdateImport(ByVal imp As Import, ByVal user As String) As Boolean
    '    Dim ps As New ArrayList
    '    Dim ok As Boolean = True

    '    Using cnn As New SqlConnection(ConnectionString)
    '        cnn.Open()

    '        Using cmd As SqlCommand = cnn.CreateCommand()

    '            cmd.CommandText = "sprInsertUpdate_Import"
    '            cmd.CommandType = CommandType.StoredProcedure

    '            cmd.Parameters.Add("@importId", SqlDbType.Int).Value = imp.IdAsInt
    '            cmd.Parameters.Add("@importTypeId", SqlDbType.Int).Value = Utility.GetDBInt(imp.ImportTypeId)
    '            cmd.Parameters.Add("@fileName", SqlDbType.NVarChar).Value = Utility.GetDBString(imp.FileName)
    '            cmd.Parameters.Add("@source", SqlDbType.NVarChar).Value = Utility.GetDBString(imp.Source)
    '            cmd.Parameters.Add("@importDate", SqlDbType.DateTime).Value = Utility.GetDBDate(imp.Date)
    '            cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = user

    '            Dim newId As Object = cmd.ExecuteScalar()

    '            If Not newId Is DBNull.Value AndAlso imp.IdAsInt = -1 Then imp.IdAsInt = newId

    '        End Using

    '        If cnn.State <> ConnectionState.Closed Then cnn.Close()
    '    End Using

    '    Return ok
    'End Function

#End Region

#Region "Ranks"

    Public Function GetRanks() As List(Of TaxonNamesInterface.Rank) Implements INamesData.GetRanks
        Dim ranks As New List(Of TaxonNamesInterface.Rank)

        Dim db As New NamesDB(ConnectionString)

        Dim ds = From tbl In db.Ranks Select tbl

        For Each r As Rank In ds
            Dim rnk As New TaxonNamesInterface.Rank
            rnk.Abbreviation = r.Abbreviation
            If r.ChangeDate.HasValue Then rnk.ChangeDate = r.ChangeDate.Value
            rnk.ChangeUser = r.ChangeUser
            rnk.KnownAbbreviations = r.KnownAbbreviations
            rnk.Name = r.Name
            rnk.RankId = r.RankId
            If r.Sort.HasValue Then rnk.Sort = r.Sort.Value
            rnk.TCSRank = r.TCSRank
            ranks.Add(rnk)
        Next

        Return ranks
    End Function

    Public Function RankById(ByVal id As Guid) As TaxonNamesInterface.Rank Implements INamesData.RankById
        Dim rnk As TaxonNamesInterface.Rank = Nothing
        Dim db As New NamesDB(ConnectionString)

        Dim ds = From tbl In db.Ranks Where tbl.RankId = id Select tbl
        If ds.Count > 0 Then
            rnk = New TaxonNamesInterface.Rank
            rnk.Abbreviation = ds.First.Abbreviation
            If ds.First.ChangeDate.HasValue Then rnk.ChangeDate = ds.First.ChangeDate.Value
            rnk.ChangeUser = ds.First.ChangeUser
            rnk.KnownAbbreviations = ds.First.KnownAbbreviations
            rnk.Name = ds.First.Name
            rnk.RankId = ds.First.RankId
            If ds.First.Sort.HasValue Then rnk.Sort = ds.First.Sort.Value
            rnk.TCSRank = ds.First.TCSRank
        End If

        Return rnk
    End Function

    Public Function RankByName(ByVal name As String) As TaxonNamesInterface.Rank Implements INamesData.RankByName
        Dim rnk As TaxonNamesInterface.Rank = Nothing
        Dim db As New NamesDB(ConnectionString)

        Dim ds = From tbl In db.Ranks Where tbl.Name = name Select tbl
        If ds.Count > 0 Then
            rnk = New TaxonNamesInterface.Rank
            rnk.Abbreviation = ds.First.Abbreviation
            If ds.First.ChangeDate.HasValue Then rnk.ChangeDate = ds.First.ChangeDate.Value
            rnk.ChangeUser = ds.First.ChangeUser
            rnk.KnownAbbreviations = ds.First.KnownAbbreviations
            rnk.Name = ds.First.Name
            rnk.RankId = ds.First.RankId
            If ds.First.Sort.HasValue Then rnk.Sort = ds.First.Sort.Value
            rnk.TCSRank = ds.First.TCSRank
        End If

        Return rnk
    End Function
#End Region

End Class
